﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using AlarmManagePlatform.Model.DB;

namespace AlarmManagePlatform.DataAccess
{
    public class tb_DataCollectTerminalInfo
    {
        internal List<string> GetNameList(Dictionary<string, string> logisticList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<string> _Result;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _Result = new List<string>();
                _SqlScript = "  Select Distinct Name From tb_DataCollectTerminalInfo";
                _SqlScript += $"    Where  [Id] > 0 ";

                if (logisticList.ContainsKey("Name") && !string.IsNullOrEmpty(logisticList["Name"]))
                {
                    _SqlScript += $"   And [Name] Like '%{logisticList["Name"]}%'";
                }

                if (logisticList.ContainsKey("IMEI") && !string.IsNullOrEmpty(logisticList["IMEI"]))
                {
                    _SqlScript += $"   And [IMEI] Like '%{logisticList["IMEI"]}%'";
                }

                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                if (_DataTable.Rows.Count > 0)
                {
                    for (int i = 0; i < _DataTable.Rows.Count; i++)
                    {
                        _Result.Add(_DataTable.Rows[i]["Name"].ToString());
                    }
                }

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取角色信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal bool CheckIsExist(string name, string iMEI,int Id)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript;
                sqlScript = string.Format("Select Id From tb_DataCollectTerminalInfo Where [Name] = '{0}' And IMEI = '{1}'", name, iMEI);

                if (Id != 0)
                {
                    sqlScript += $" And [Id] != {Id} ";
                }

                sqlDb = Static.Methods.OpenDataBase();
                if (sqlDb.RunSqlScript(sqlScript).Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception("检查【采集终端】是否存在失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal List<Model.DB.tb_DataCollectTerminalInfo> GetCollectTerminalInfoList(string name, Dictionary<string, string> logisticList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.tb_DataCollectTerminalInfo> _Result;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _SqlScript = $"Select * From tb_DataCollectTerminalInfo Where Name = '{name}' ";
                
                if (logisticList.ContainsKey("IMEI") && !string.IsNullOrEmpty(logisticList["IMEI"]))
                {
                    _SqlScript += $"   And [IMEI] Like '%{logisticList["IMEI"]}%'";
                }

                _SqlScript += " Order By Id";
                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);
                _Result = DataTableToList(_DataTable);

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取采集终端信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }


        internal Model.DB.tb_DataCollectTerminalInfo GetInfoForId(string id)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.tb_DataCollectTerminalInfo> _Result;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _SqlScript = $"Select * From tb_DataCollectTerminalInfo Where [Id] = '{id}' ";

                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);
                _Result = DataTableToList(_DataTable);

                if (_Result.Count > 0)
                    return _Result[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw new Exception("获取采集终端信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void DeleteCollectTerminal(List<string> indexList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _Ids = string.Empty;
            string _SqlScript = string.Empty;
            try
            {
                _SqlDb = Static.Methods.OpenDataBase();
                _Ids = Static.Methods.CombiningStrings2<string>(indexList);

                _SqlScript = "Delete From tb_DataCollectTerminalInfo";
                _SqlScript += $" Where Id in ( {_Ids} )";

                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("删除采集终端信息，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void SaveCollectTerminalInfo(Model.DB.tb_DataCollectTerminalInfo dataCollectTerminalInfo)
        {
            //  Insert into tb_DataCollectTerminalInfo ([Name],[IMEI],[CreateTime])
            //values('名称4', '44444444', '2018-11-21')

            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                _SqlScript += "     Insert into tb_DataCollectTerminalInfo ([Name],[IMEI],[CreateTime])";

                _SqlScript += $"     Values('{dataCollectTerminalInfo.Name}', '{dataCollectTerminalInfo.IMEI}', '{DateTime.Now.ToString(Static.ParaInfo.DateFormatLong)}')";

                _SqlDb = Static.Methods.OpenDataBase();
                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("保存采集终端失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void UpdateDataCollectTerminal(Model.DB.tb_DataCollectTerminalInfo dataCollectTerminalInfo)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                _SqlScript += $"  Update tb_DataCollectTerminalInfo Set [Name] = '{dataCollectTerminalInfo.Name}',[IMEI] = '{dataCollectTerminalInfo.IMEI}'";
                _SqlScript += $"  Where [Id] = {dataCollectTerminalInfo.Id}";

                _SqlDb = Static.Methods.OpenDataBase();
                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("修改采集终端失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal List<GPSInfo> GetGPSInfo()
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.GPSInfo> _Result;
            Model.DB.GPSInfo _Temp = null;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _Result = new List<GPSInfo>();

                _SqlScript = "Select id,Name,Latitude,Longitude,GPSDesc,uploadDate From tb_DataCollectTerminalInfo Order By id";

                _SqlDb = Static.Methods.OpenDataBase();

                _DataTable = _SqlDb.RunSqlScript(_SqlScript);
                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    if (!Relations.Common.Methods.IsFloat(_DataTable.Rows[i]["Latitude"].ToString()) || !Relations.Common.Methods.IsFloat(_DataTable.Rows[i]["Longitude"].ToString())) continue;

                    _Temp = new Model.DB.GPSInfo();
                    _Temp.Id = _DataTable.Rows[i]["id"].ToString();
                    _Temp.Name = _DataTable.Rows[i]["Name"].ToString();
                    _Temp.Latitude = float.Parse(_DataTable.Rows[i]["Latitude"].ToString());
                    _Temp.Longitude = float.Parse(_DataTable.Rows[i]["Longitude"].ToString());
                    _Temp.GpsDesc = _DataTable.Rows[i]["GPSDesc"].ToString();

                    if (!string.IsNullOrEmpty(_DataTable.Rows[i]["uploadDate"].ToString()))
                        _Temp.UploadDate = DateTime.Parse(_DataTable.Rows[i]["uploadDate"].ToString()).ToString(Static.ParaInfo.DateFormatLong);

                    _Result.Add(_Temp);
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取采集终端GPS信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        private List<Model.DB.tb_DataCollectTerminalInfo> DataTableToList(DataTable dataTable)
        {
            List<Model.DB.tb_DataCollectTerminalInfo> _Result;
            Model.DB.tb_DataCollectTerminalInfo _Temp;
            try
            {
                _Result = new List<Model.DB.tb_DataCollectTerminalInfo>();
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    _Temp = new Model.DB.tb_DataCollectTerminalInfo();
                    _Temp.Id = int.Parse(dataTable.Rows[i]["Id"].ToString());
                    _Temp.Name = dataTable.Rows[i]["Name"].ToString();
                    _Temp.IMEI = dataTable.Rows[i]["IMEI"].ToString();

                    if (!string.IsNullOrEmpty(dataTable.Rows[i]["CreateTime"].ToString()))
                        _Temp.CreateTime = DateTime.Parse(dataTable.Rows[i]["CreateTime"].ToString()).ToString(Static.ParaInfo.DateFormatLong);

                    _Result.Add(_Temp);
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("将Datatable转换成List<Model.DB.tb_DataCollectTerminalInfo>有异常：" + ex.ToString());
            }
        }
    }
}
